using System;
using System.Data;
using System.Data.Common;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using BaHuan.Common;
using BaHuan.Entities;
/*
 * For each DB unit, normally tables, we create a DAO class for it
 * this will enalbe us to do some basic tasks for a DB unit
 * such as Save, Delete, GetAll, GetById....
 * 
 * */
namespace BaHuan.DataLayer
{
	
	class ButToanDAO:BaseDAO
	{
	
		// save into DB, @id = -1: insert else, update
		public ResultObj SaveButToan(ButToan buttoan)
        		{
			ResultObj result = new ResultObj();
			result.Result = null;
			SqlConnection conObj = DataConnection.GetSqlConnection();
			SqlCommand cmd = CreateSqlCommand("spAddButToan", CommandType.StoredProcedure);
			cmd.CommandType = CommandType.StoredProcedure;
			cmd.CommandText = "spAddButToan";
			SqlParameter param = new SqlParameter();
			param = CreateParameter("@Id", DbType.Int32, ParameterDirection.Input, buttoan.Id);
			cmd.Parameters.Add(param);
			param = CreateParameter("@NgayThang", DbType.DateTime, ParameterDirection.Input, buttoan.NgayThang);
			cmd.Parameters.Add(param);
			param = CreateParameter("@TKGoc", DbType.String, ParameterDirection.Input, buttoan.TKGoc);
			cmd.Parameters.Add(param);
			param = CreateParameter("@TKDoiUng", DbType.String, ParameterDirection.Input, buttoan.TKDoiUng);
			cmd.Parameters.Add(param);
			param = CreateParameter("@GhiNo", DbType.Int64, ParameterDirection.Input, buttoan.GhiNo);
			cmd.Parameters.Add(param);
			param = CreateParameter("@GhiCo", DbType.Int64, ParameterDirection.Input, buttoan.GhiCo);
			cmd.Parameters.Add(param);
			param = CreateParameter("@SoChungTu", DbType.String, ParameterDirection.Input, buttoan.SoChungTu);
			cmd.Parameters.Add(param);
			param = CreateParameter("@DienGiai", DbType.String, ParameterDirection.Input, buttoan.DienGiai);
			cmd.Parameters.Add(param);
			param = CreateParameter("@MaSoKhachHang", DbType.String, ParameterDirection.Input, buttoan.MaSoKhachHang);
			cmd.Parameters.Add(param);
			param = CreateParameter("@ChiNhanh", DbType.String, ParameterDirection.Input, buttoan.ChiNhanh);
			cmd.Parameters.Add(param);
            param = CreateParameter("@IsEdited", DbType.String, ParameterDirection.Input, buttoan.IsEdited);
            cmd.Parameters.Add(param);
            param = CreateParameter("@ReturnVal", DbType.String, ParameterDirection.ReturnValue, result.ReturnCode);
            cmd.Parameters.Add(param);
            conObj.Open();
            cmd.ExecuteNonQuery();
            conObj.Close();

            result.ReturnCode = Convert.ToInt32(cmd.Parameters["@ReturnVal"].Value);
			return result;
		}
		
		// Get all ButToan in DB
		public ResultObj GetAllButToan( )
        		{
			ResultObj result = new ResultObj();
			result.Result = null;
			List<ButToan> lst = null;
			SqlCommand cmd = CreateSqlCommand("spGetAllButToan", CommandType.StoredProcedure);
			cmd.Connection.Open();
			using (SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
			{
			lst = new List<ButToan>();
			ButToan item = null;
			// reader all data from DB one by one
			while (reader.Read())
			{
			item = new ButToan();
			item.Id = Convert.ToInt32(reader["Id"]);
			item.NgayThang = Convert.ToDateTime(reader["NgayThang"]);
			item.TKGoc = Convert.ToString(reader["TKGoc"]);
			item.TKDoiUng = Convert.ToString(reader["TKDoiUng"]);
			item.GhiNo = Convert.ToInt64(reader["GhiNo"]);
			item.GhiCo = Convert.ToInt64(reader["GhiCo"]);
			item.SoChungTu = Convert.ToString(reader["SoChungTu"]);
			item.DienGiai = Convert.ToString(reader["DienGiai"]);
			item.MaSoKhachHang = Convert.ToString(reader["MaSoKhachHang"]);
			item.ChiNhanh = Convert.ToString(reader["ChiNhanh"]);
			lst.Add(item);
			}
			}
			result.Result = lst;
			result.ReturnCode = 1;
			
			return result;
		}
		
		// Get all ButToan in DB with condition
		public ResultObj GetAllButToanByID(ButToan buttoan)
        		{
			ResultObj result = new ResultObj();
			result.Result = null;
			List<ButToan> lst = null;
			SqlCommand cmd = CreateSqlCommand("spGetAllButToanByID", CommandType.StoredProcedure);
			SqlParameter param = CreateParameter("@Id", DbType.Int32, ParameterDirection.Input, buttoan.Id);
			cmd.Parameters.Add(param);
			cmd.Connection.Open();
			using (SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
			{
			lst = new List<ButToan>();
			ButToan item = null;
			// reader all data from DB one by one
			while (reader.Read())
			{
			item = new ButToan();
			item.Id = Convert.ToInt32(reader["Id"]);
			item.NgayThang = Convert.ToDateTime(reader["NgayThang"]);
			item.TKGoc = Convert.ToString(reader["TKGoc"]);
			item.TKDoiUng = Convert.ToString(reader["TKDoiUng"]);
			item.GhiNo = Convert.ToInt64(reader["GhiNo"]);
			item.GhiCo = Convert.ToInt64(reader["GhiCo"]);
			item.SoChungTu = Convert.ToString(reader["SoChungTu"]);
			item.DienGiai = Convert.ToString(reader["DienGiai"]);
			item.MaSoKhachHang = Convert.ToString(reader["MaSoKhachHang"]);
			item.ChiNhanh = Convert.ToString(reader["ChiNhanh"]);
			lst.Add(item);
			}
			}
			result.Result = lst;
			result.ReturnCode = 1;
			
			return result;
		}

        public ResultObj GetAllButToanForCongNoPhaiTra()
        {
            ResultObj result = new ResultObj();
            result.Result = null;
            List<ButToan> lst = null;
            SqlCommand cmd = CreateSqlCommand("spGetAllButToanForCongNoPhaiTra", CommandType.StoredProcedure);
            cmd.Connection.Open();
            using (SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
            {
                lst = new List<ButToan>();
                ButToan item = null;
                // reader all data from DB one by one
                while (reader.Read())
                {
                    item = new ButToan();
                    item.NgayThang = Convert.ToDateTime(reader["NgayThang"]);
                    item.TKDoiUng = Convert.ToString(reader["TKDoiUngList"]);
                    item.GhiNo = Convert.ToInt64(reader["SumGhiNo"]);
                    item.GhiCo = Convert.ToInt64(reader["SumGhiCo"]);
                    item.SoChungTu = Convert.ToString(reader["SoChungTu"]);
                    item.DienGiai = Convert.ToString(reader["DienGiai"]);
                    item.MaSoKhachHang = Convert.ToString(reader["MaSoKhachHang"]);
                    lst.Add(item);
                }
            }
            result.Result = lst;
            result.ReturnCode = 1;

            return result;
        }

        public ResultObj GetAllButToanForCongNoPhaiThu()
        {
            ResultObj result = new ResultObj();
            result.Result = null;
            List<ButToan> lst = null;
            SqlCommand cmd = CreateSqlCommand("spGetAllButToanForCongNoPhaiThu", CommandType.StoredProcedure);
            cmd.Connection.Open();
            using (SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
            {
                lst = new List<ButToan>();
                ButToan item = null;
                // reader all data from DB one by one
                while (reader.Read())
                {
                    item = new ButToan();
                    item.NgayThang = Convert.ToDateTime(reader["NgayThang"]);
                    item.TKDoiUng = Convert.ToString(reader["TKDoiUngList"]);
                    item.GhiNo = Convert.ToInt64(reader["SumGhiNo"]);
                    item.GhiCo = Convert.ToInt64(reader["SumGhiCo"]);
                    item.SoChungTu = Convert.ToString(reader["SoChungTu"]);
                    item.DienGiai = Convert.ToString(reader["DienGiai"]);
                    item.MaSoKhachHang = Convert.ToString(reader["MaSoKhachHang"]);
                    item.ChiNhanh = Convert.ToString(reader["ChiNhanhKhachHang"]);
                    item.TKGoc = Convert.ToString(reader["SoHoaDonDo"]);
                    lst.Add(item);
                }
            }
            result.Result = lst;
            result.ReturnCode = 1;

            return result;
        }

        public ResultObj GetButToanForCongNoTongHop111(DateTime dtTuNgay, DateTime dtDenNgay)
        {
            ResultObj result = new ResultObj();
            result.Result = null;
            List<ButToan> lst = null;
            SqlCommand cmd = CreateSqlCommand("spGetButToanForCongNoTongHop111", CommandType.StoredProcedure);
            SqlParameter param = CreateParameter("@TuNgay", DbType.DateTime, ParameterDirection.Input, dtTuNgay);
            cmd.Parameters.Add(param);
            param = CreateParameter("@DenNgay", DbType.DateTime, ParameterDirection.Input, dtDenNgay);
            cmd.Parameters.Add(param);
            cmd.Connection.Open();
            using (SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
            {
                lst = new List<ButToan>();
                ButToan item = null;
                // reader all data from DB one by one
                while (reader.Read())
                {
                    item = new ButToan();
                    item.GhiNo = Convert.ToInt64(reader["SumGhiNo"]);
                    item.GhiCo = Convert.ToInt64(reader["SumGhiCo"]);
                    item.ChiNhanh = Convert.ToString(reader["ChiNhanh"]);
                    lst.Add(item);
                }
            }
            result.Result = lst;
            result.ReturnCode = 1;

            return result;
        }

        public ResultObj GetButToanForCongNoTongHop112(DateTime dtTuNgay, DateTime dtDenNgay)
        {
            ResultObj result = new ResultObj();
            result.Result = null;
            List<ButToan> lst = null;
            SqlCommand cmd = CreateSqlCommand("spGetButToanForCongNoTongHop112", CommandType.StoredProcedure);
            SqlParameter param = CreateParameter("@TuNgay", DbType.DateTime, ParameterDirection.Input, dtTuNgay);
            cmd.Parameters.Add(param);
            param = CreateParameter("@DenNgay", DbType.DateTime, ParameterDirection.Input, dtDenNgay);
            cmd.Parameters.Add(param);
            cmd.Connection.Open();
            using (SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
            {
                lst = new List<ButToan>();
                ButToan item = null;
                // reader all data from DB one by one
                while (reader.Read())
                {
                    item = new ButToan();
                    item.GhiNo = Convert.ToInt64(reader["GhiNo"]);
                    item.GhiCo = Convert.ToInt64(reader["GhiCo"]);
                    item.ChiNhanh = Convert.ToString(reader["NganHang"]); // Temp field to store records of field NganHang
                    lst.Add(item);
                }
            }
            result.Result = lst;
            result.ReturnCode = 1;

            return result;
        }
		
		// Delete a ButToan out of DB with condition
		public ResultObj DeleteButToan(ButToan buttoan)
        		{
			ResultObj result = new ResultObj();
			result.Result = null;
			SqlConnection conObj = DataConnection.GetSqlConnection();
			SqlCommand cmd = CreateSqlCommand("spDeleteButToan", CommandType.StoredProcedure);
			cmd.CommandType = CommandType.StoredProcedure;
			cmd.CommandText = "spDeleteButToan";
			SqlParameter param = CreateParameter("@Id", DbType.Int32, ParameterDirection.Input, buttoan.Id);
			cmd.Parameters.Add(param);
			conObj.Open();
			result.ReturnCode = cmd.ExecuteNonQuery();
			conObj.Close();
			
			return result;
		}

        public ResultObj GetAllButtoanForBCDTK(DateTime dt, string sMaSoTK)
        {
            ResultObj result = new ResultObj();
            result.Result = null;
            List<ButToanWithUNC> lst = null;

            SqlCommand cmd = CreateSqlCommand("spGetAllButtoanForBCDTK", CommandType.StoredProcedure);
            SqlParameter param = CreateParameter("@Ngay", DbType.DateTime, ParameterDirection.Input, dt);
            cmd.Parameters.Add(param);
            param = CreateParameter("@MaSoTK", DbType.String, ParameterDirection.Input, sMaSoTK);
            cmd.Parameters.Add(param);

            cmd.Connection.Open();
            using (SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
            {
                lst = new List<ButToanWithUNC>();
                ButToanWithUNC item = null;
                // reader all data from DB one by one
                while (reader.Read())
                {
                    item = new ButToanWithUNC();
                    item.IdBT = Convert.ToInt32(reader["Id"]);
                    item.NgayThang = Convert.ToDateTime(reader["NgayThang"]);
                    item.TKGoc = Convert.ToString(reader["TKGoc"]);
                    item.TKDoiUng = Convert.ToString(reader["TKDoiUng"]);
                    item.GhiNo = Convert.ToInt64(reader["GhiNo"]);
                    item.GhiCo = Convert.ToInt64(reader["GhiCo"]);
                    item.SoChungTu = Convert.ToString(reader["SoChungTu"]);
                    item.DienGiai = Convert.ToString(reader["DienGiai"]);
                    item.MaSoKhachHang = Convert.ToString(reader["MaSoKhachHang"]);
                    item.ChiNhanh = Convert.ToString(reader["ChiNhanh"]);

                    item.IdUNC = reader["IdUNC"] != DBNull.Value ? Convert.ToInt32(reader["IdUNC"]) : 0;
                    item.MaSoUNC = reader["MaSoUNC"] != DBNull.Value ? Convert.ToString(reader["MaSoUNC"]) : "";
                    item.NgayThangUNC = reader["NgayThangUNC"] != DBNull.Value ? Convert.ToDateTime(reader["NgayThangUNC"]) : DateTime.Now;
                    item.IdChiNhanhCongTyUNC = reader["IdChiNhanhCongTy"] != DBNull.Value ? Convert.ToInt32(reader["IdChiNhanhCongTy"]) : 0;
                    item.IdNganHangUNC = reader["IdNganHang"] != DBNull.Value ? Convert.ToInt32(reader["IdNganHang"]) : 0;
                    item.IdTaiKhoanGiaoDichUNC = reader["IdTaiKhoanGiaoDich"] != DBNull.Value ? Convert.ToInt32(reader["IdTaiKhoanGiaoDich"]) : 0;
                    item.IdNhaCungCap = reader["IdNhaCungCap"] != DBNull.Value ? Convert.ToInt32(reader["IdNhaCungCap"]) : 0;
                    item.IdNganHangNhanUNC = reader["IdNganHangNhan"] != DBNull.Value ? Convert.ToInt32(reader["IdNganHangNhan"]) : 0;
                    item.IdTaiKhoanNhanUNC = reader["TaiKhoanNhan"] != DBNull.Value ? Convert.ToInt32(reader["IdTaiKhoanNhan"]) : 0;
                    item.NoiDungUNC = reader["NoiDung"] != DBNull.Value ? Convert.ToString(reader["NoiDung"]) : "";
                    item.SoTienUNC = reader["SoTien"] != DBNull.Value ? Convert.ToInt64(reader["SoTien"]) : 0;
                    item.NguoiLapUNC = reader["NguoiLap"] != DBNull.Value ? Convert.ToString(reader["NguoiLap"]) : "";
                    item.TenNganHang = reader["TenNganHang"] != DBNull.Value ? Convert.ToString(reader["TenNganHang"]) : "";

                    lst.Add(item);
                }
            }
            result.Result = lst;
            result.ReturnCode = 1;

            return result;
        }
	}
}
